﻿
using HDSmartCommunity.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace HDSmartCommunity.DAL
{
    public class CardInfoDAL
    {
        public List<VoucherInfo> GetAllVoucher()
        {
            string strSql = "SELECT * FROM cardinfo";

            CommFN<VoucherInfo> fn = new CommFN<VoucherInfo>();
            return fn.GetDataList(strSql);
        }

        public VoucherInfo GetVoucherByID(int vid)
        {
            string strSql = "SELECT * FROM cardinfo where id=" + vid;

            CommFN<VoucherInfo> fn = new CommFN<VoucherInfo>();
            return fn.GetData(strSql);
        }

        public bool UpdateVoucherByID(VoucherInfo info)
        {
            string strSql = string.Format("UPDATE cardinfo SET cardID='{0}',StartTime='{1}',OverTime='{2}',subDeviceID='{3}',UserType='{4}',CredenceType={5},username='{6}',UUID='{7}',elevatorAuthFloor='{8}' WHERE id={9}"
                , info.cardID, info.StartTime, info.OverTime, info.subDeviceID, info.UserType, info.CredenceType, info.userName, info.UUID, info.elevatorAuthFloor, info.ID);

            CommFN<VoucherInfo> fn = new CommFN<VoucherInfo>();
            return fn.ExecFN(strSql);
        }

        public bool InsertVoucherInfo(VoucherInfo info)
        {
            try
            {
                string strSql = string.Format("INSERT INTO cardinfo(cardID,StartTime,OverTime,subDeviceID,UserType,CredenceType,username,UUID,elevatorAuthFloor)VALUES('{0}','{1}','{2}','{3}',{4},{5},'{6}','{7}','{8}')"
                    , info.cardID, info.StartTime, info.OverTime, info.subDeviceID, info.UserType, info.CredenceType, info.userName, info.UUID, info.elevatorAuthFloor);

                CommFN<VoucherInfo> fn = new CommFN<VoucherInfo>();
                return fn.ExecFN(strSql);
            }
            catch
            {
                return false;
            }
        }

        public bool DeleteVoucherInfo(VoucherInfo info)
        {
            try
            {
                string strSql = "DELETE FROM cardinfo WHERE ID=" + info.ID;

                CommFN<VoucherInfo> fn = new CommFN<VoucherInfo>();
                return fn.ExecFN(strSql);
            }
            catch
            {
                return false;
            }
        }

        public List<VoucherInfo> QueryVoucher(VoucherInfo voucher)
        {
            string sqlwhere = string.Empty;
            string strSql = "SELECT * FROM cardinfo as a LEFT JOIN credencetype as b on a.CredenceType=b.ID LEFT JOIN role as c on a.UserType=c.ID";
            if (!string.IsNullOrEmpty(voucher.UUID))
            {
                sqlwhere += "UUID like '%" + voucher.UUID + "%' and";
            }
            if (!string.IsNullOrEmpty(voucher.cardID))
            {
                sqlwhere += "cardID like '%" + voucher.cardID + "%' and";
            }
            if (sqlwhere.Length > 0)
            {
                strSql = strSql + " where " + sqlwhere.Substring(0, sqlwhere.Length - 4);
            }
            if (voucher.Recordhead > -1 && voucher.Recordtail > 0)
            {
                strSql = strSql + " limit " + voucher.Recordhead + "," + voucher.Recordtail;
            }

            CommFN<VoucherInfo> fn = new CommFN<VoucherInfo>();
            return fn.GetDataList(strSql);
        }

        public int QueryVoucherCount(VoucherInfo voucher)
        {
            string sqlwhere = string.Empty;
            string strSql = "SELECT COUNT(1) FROM cardinfo";
            if (!string.IsNullOrEmpty(voucher.UUID))
            {
                sqlwhere += "UUID like '%" + voucher.UUID + "%' and";
            }
            if (!string.IsNullOrEmpty(voucher.cardID))
            {
                sqlwhere += "cardID like '%" + voucher.cardID + "%' and";
            }
            if (sqlwhere.Length > 0)
            {
                strSql = strSql + " where " + sqlwhere.Substring(0, sqlwhere.Length - 4);
            }

            CommFN<VoucherInfo> fn = new CommFN<VoucherInfo>();
            return Convert.ToInt32(fn.ExecuteScalar(strSql));
        }

        public List<FloorInfo> GetFloor()
        {

            string strSql = "SELECT * FROM floor ORDER by actualfloor ASC";
            CommFN<FloorInfo> fn = new CommFN<FloorInfo>();
            return fn.GetDataList(strSql);
        }

        public List<FloorInfo> GetShowFloor(string floor)
        {
            string strSql = "SELECT displayfloor FROM floor WHERE actualfloor in(" + floor + ")";
            CommFN<FloorInfo> fn = new CommFN<FloorInfo>();
            return fn.GetDataList(strSql);
        }

        public List<Role> GetRole()
        {
            try
            {
                string strSql = "SELECT * FROM role";
                CommFN<Role> fn = new CommFN<Role>();
                return fn.GetDataList(strSql);
            }
            catch
            {
                return null;
            }
        }

        public List<CredenceType> GetCredenceType()
        {
            try
            {
                string strSql = "SELECT * FROM credencetype";
                CommFN<CredenceType> fn = new CommFN<CredenceType>();
                return fn.GetDataList(strSql);
            }
            catch
            {
                return null;
            }
        }
    }


}
